﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using MySql.Data.Types;

namespace Abigail
{
    /// <summary>
    /// This class is the link to the Database.
    /// Whitin is a switch for testdb/realdb
    /// The connection stays open for the duration of the instance.
    /// </summary>
    class Db
    {
        #region Variables
        private bool testing = true;    // Control which db to use:
        public bool connectionExists;   // Last time checked
        private MySqlConnection conn;   // Holds the connection
        private Controller c;           // Holds reference to the controller who instanciated us
        #endregion

        #region Basic
        /// <summary>
        /// Instanciates the Db and connects.
        /// </summary>
        /// <param name="givenC">The controller who instanciates this DB</param>
        public Db(Controller givenC)
        {
            connectionExists = Connect();
            c = givenC;

            if (connectionExists)
                WriteLogWithComment("DB000", c.IP);
        }
        
        /// <summary>
        /// Connect to the Database
        /// </summary>
        /// <returns>True if success, false otherwise</returns>
        public bool Connect()
        {
            string host = "localhost";
            string database = "abigail";
            string user = "root";
            string password = "";

            if (testing)
            {
                host = "localhost";
                database = "abigail";
                user = "root";
                password = "";
            }

            string strBuilder = "Data Source=" + host + ";Database=" + database + ";User ID=" + user + ";Password=" + password;

            try
            {
                conn = new MySqlConnection(strBuilder);
                conn.Open();
                return true;
            }
            catch (MySqlException e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
        }

        /// <summary>
        /// Check current connection for the state open.
        /// Also sets the instance variable connectionExists to same as return value.
        /// </summary>
        /// <returns>Returns true if connection has state Open.</returns>
        public bool CheckConnection()
        {
            if (conn.State == ConnectionState.Open)
                connectionExists = true;
            else
                connectionExists = false;
            return connectionExists;
        }

        /// <summary>
        /// Standard method for sending a query (select) to the database.
        /// </summary>
        /// <param name="queryString">The SQL-string you want to query.</param>
        /// <returns>Result from SQL-string as DataReader object.</returns>
        private MySqlDataReader Query(string queryString)
        {
            if (!CheckConnection())
                if (!Connect())
                {
                    Popup.Error("Anslutning till databas misslyckades trots upprepade försök\nProgrammet kommer att avslutas.", "Ohanterbart fel");
                    Controller.ExitApplication();
                    return null;
                }
            try
            {
                MySqlCommand mysqlCmd = new MySqlCommand(queryString, conn);
                return mysqlCmd.ExecuteReader();
            }
            catch (MySqlException e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return null;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return null;
            }
        }

        /// <summary>
        /// Standard method for sending a non query (insert, delete, update) to the database.
        /// </summary>
        /// <param name="nonQueryString">The SQL-string you want to perform.</param>
        /// <returns>Result from SQL-string as DataReader object.</returns>
        private int NonQuery(string nonQueryString)
        {
            if (!CheckConnection())
                if (!Connect())
                {
                    Popup.Error("Anslutning till databas misslyckades trots upprepade försök\nProgrammet kommer att avslutas.", "Ohanterbart fel");
                    Controller.ExitApplication();
                    return 0;
                }

            try
            {
                MySqlCommand mysqlCmd = new MySqlCommand(nonQueryString, conn);
                return mysqlCmd.ExecuteNonQuery();
            }
            catch (MySqlException e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return 0;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return 0;
            }
        }

        #endregion

        #region User
        /// <summary>
        /// Returns all users in the database as a datatable.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <returns>A datatable with strings.</returns>
        public DataTable GetAllUsersAsDataTable()
        {
            // Create datatable:
            DataTable table = new DataTable();
            table.Columns.Add("Initialer", typeof(string));
            table.Columns.Add("Namn", typeof(string));
            table.Columns.Add("E-post", typeof(string));
            table.Columns.Add("Telefon", typeof(string));
            table.Columns.Add("Status", typeof(string));
            table.Columns.Add("Updaterad", typeof(string));

            // Create Query:
            MySqlDataReader result = null;
            try
            {
                string selectString = String.Format("select initials, name, email, phone, activated, updated");
                string fromString = String.Format("from `users` where initials != 'SYS'");
                string queryString = String.Format("{0} {1}", selectString, fromString);
                result = Query(queryString.ToString());

                while (result.Read())
                {
                    //Extract user
                    Model.User user = new Model.User();
                    user.Initials = result.GetString(0);
                    user.Name = result.GetString(1);
                    if (!result.IsDBNull(2))
                        user.Email = result.GetString(2);
                    else
                        user.Email = "";
                    if (!result.IsDBNull(3))
                        user.Phone = result.GetString(3);
                    else
                        user.Phone = "";
                    string activated;
                    if (result.GetInt16(4) == 1)
                        activated = "Aktiverad";
                    else
                        activated = "Inaktiverad";
                    user.Updated = result.GetDateTime(5);
                    table.Rows.Add(user.Initials, user.Name, user.Email, user.Phone, activated, user.Updated.ToShortDateString() + " " + user.Updated.ToShortTimeString());
                }

            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return null;
            }
            finally
            {
                result.Close();
            }
            return table;
        }

        /// <summary>
        /// Counts the number of users in the database.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <returns>Number of users as int.</returns>
        public int GetUserCount()
        {
            MySqlDataReader result = null;
            try
            {
                string selectString = String.Format("select count(initials)");
                string fromString = String.Format("from `users`");
                string whereString = String.Format("where initials != 'SYS'");

                string queryString = String.Format("{0} {1} {2}", selectString, fromString, whereString);
                result = Query(queryString.ToString());
                result.Read();
                return result.GetInt16(0);
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return 0;
            }
            finally
            {
                result.Close();
            }
        }

        /// <summary>
        /// Returns an array of strings containing all the user initials found in the database.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <returns>Array of user initials</returns>
        public string[] GetUserInitials()
        {
            int count = GetUserCount();
            string[] initials = new string[count];

            // Create Query:
            MySqlDataReader result = null;
            try
            {
                string selectString = String.Format("select initials");
                string fromString = String.Format("from `users` where initials != 'SYS'");
                string queryString = String.Format("{0} {1}", selectString, fromString);
                result = Query(queryString.ToString());
                int index = 0;
                while (result.Read())
                {
                    initials[index] = result.GetString(0);
                    index++;
                }
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return null;
            }
            finally
            {
                result.Close();
            }
            return initials;
        }

        /// <summary>
        /// Fetches the corresponding user from the database.
        /// Require that there is no open Result on connection.
        /// </summary>
        /// <remarks>- Requires there is no open Result on connection.</remarks>
        /// <param name="initials">The initials for the user (Primary Key)</param>
        /// <returns>The fetched user as an User object.</returns>
        public Model.User GetUserFromInitials(string initials)
        {
            MySqlDataReader result = null;
            try
            {
                string selectString = String.Format("select initials, name, password, email, phone, activated");
                string fromString = String.Format("from `users`");
                string whereString = String.Format("where initials = '{0}'", initials);

                string queryString = String.Format("{0} {1} {2}", selectString, fromString, whereString);
                result = Query(queryString.ToString());

                if (result == null)
                {
                    result.Close();
                    return null;
                }

                result.Read();
                if (!result.HasRows)
                {
                    result.Close();
                    return null;
                }

                //Extract user
                Model.User user = new Model.User();
                user.Initials = result.GetString(0);
                user.Name = result.GetString(1);
                user.PasswordInSHA1 = result.GetString(2);
                if (!result.IsDBNull(3))
                    user.Email = result.GetString(3);
                if (!result.IsDBNull(4))
                    user.Phone = result.GetString(4);

                if (result.GetInt16(5) == 1)
                    user.Activated = true;
                else
                    user.Activated = false;

                result.Close();
                return user;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return null;
            }
            finally
            {
                result.Close();
            }
        }

        /// <summary>
        /// Updates the user corresponding to the initials provided in the User object from the database.
        /// Does not update password.
        /// Require that there is no open Result on connection.
        /// </summary>
        /// <remarks>
        /// - Password is not changed.
        /// - Requires there is no open Result on connection.
        /// - User id is not updated
        /// </remarks>
        /// <param name="user">The user to be updated (id and password is not updated)</param>
        /// <returns>True on success</returns>
        public bool UpdateUser(Model.User user)
        {
            // First: Verify that the initials exists:
            if (!VerifyUserInitialsExists(user.Initials))
            {
                Popup.Information("Det finns ingen användare med initialerna " + user.Initials, "Fel");
                return false;
            }

            // Second: Build nonQueryString
            try
            {
                int activated;
                if (user.Activated)
                    activated = 1;
                else
                    activated = 0;

                string updateString = String.Format("update `users`");
                string setString = String.Format("set name = '{0}', email = '{1}', phone = '{2}', activated = '{3}'", user.Name, user.Email, user.Phone, activated);
                string whereString = String.Format("where initials = '{0}'", user.Initials);

                string nonQueryString = String.Format("{0} {1} {2}", updateString, setString, whereString);
                if (NonQuery(nonQueryString.ToString()) == 1)
                    return true;
                else
                    throw new IndexOutOfRangeException("Indexfel i databas.");;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
        }

        /// <summary>
        /// Updates the user's password. Looks up user by provided user object's property Id.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <param name="user">User object containing AT LEAST id and new password</param>
        /// <returns>True on success</returns>
        public bool UpdateUserPassword(Model.User user)
        {
            // First: Verify that the initials exists:
            if (!VerifyUserInitialsExists(user.Initials))
            {
                Popup.Information("Det finns ingen användare med initialerna " + user.Initials, "Fel");
                return false;
            }

            // Second: Build nonQueryString
            try
            {
                string updateString = String.Format("update `users`");
                string setString = String.Format("set password = '{0}'", user.PasswordInSHA1);
                string whereString = String.Format("where initials = '{0}'", user.Initials);

                string nonQueryString = String.Format("{0} {1} {2}", updateString, setString, whereString);
                if (NonQuery(nonQueryString.ToString()) == 1)
                    return true;
                else
                    throw new IndexOutOfRangeException("Indexfel i databas.");;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
        }

        /// <summary>
        /// Checks if the user initials exists in the database.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <param name="initials">The initials to be verified.</param>
        /// <returns>True if exactly 1 (one) exists in database.</returns>
        public bool VerifyUserInitialsExists(string initials)
        {
            MySqlDataReader result = null;
            try
            {
                string selectString = String.Format("select count(initials)");
                string fromString = String.Format("from `users`");
                string whereString = String.Format("where initials = '{0}'", initials);

                string queryString = String.Format("{0} {1} {2}", selectString, fromString, whereString);
                result = Query(queryString.ToString());
                if (result == null)
                {
                    result.Close();
                    return false;
                }

                result.Read();
                if (!result.HasRows)
                {
                    result.Close();
                    return false;
                }

                if (result.GetInt16(0) == 1)
                {
                    result.Close();
                    return true;
                }
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
            finally
            {
                result.Close();
            }
            
            return false;
        }
        #endregion

        #region Log
        /// <summary>
        /// Writes a log message to to the database with current timestamp.
        /// If the logCode is found to be not valid 00000 is used instead.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// - If logCode is not valid 0000 will be used instead.
        /// </remarks>
        /// <param name="logCode">The log code to be written.</param>
        public void WriteLog(string logCode)
        {
            Model.User user = c.LoggedInUser; 
            string initials = "SYS";
            if (user != null)
                initials = user.Initials;

            if (!VerifyLogCodeExists(logCode))
                logCode = "00000";;

            // Build nonQueryString
            try
            {
                string insertIntoString = String.Format("insert into log");
                string columnString = String.Format("(who, code)");
                string valueString = String.Format("values ('{0}', '{1}')", initials, logCode);

                string nonQueryString = String.Format("{0} {1} {2}", insertIntoString, columnString, valueString);
                NonQuery(nonQueryString.ToString());
                
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return;
            }
        }

        /// <summary>
        /// Writes a log message with comment to to the database with current timestamp.
        /// If the logCode is found to be not valid 00000 is used instead.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// - If logCode is not valid 0000 will be used instead.
        /// </remarks>
        /// <param name="logCode">The log code to be written.</param>
        /// <param name="comment">Free text to comment. Max 100 characters.</param>
        public void WriteLogWithComment(string logCode, string comment)
        {
            Model.User user = c.LoggedInUser;
            string initials = "SYS";
            if (user != null)
                initials = user.Initials;

            if (!VerifyLogCodeExists(logCode))
                logCode = "00000";

            // Build nonQueryString
            try
            {
                // First insert into Log
                string insertIntoString = String.Format("insert into log");
                string columnString = String.Format("(id, who, code)");
                string valueString = String.Format("values (null, '{0}', '{1}')", initials, logCode);

                string nonQueryString1 = String.Format("{0} {1} {2};", insertIntoString, columnString, valueString);
                
                // Second insert into LogInformation
                insertIntoString = String.Format("insert into loginformation");
                columnString = String.Format("(log, additionalinformation)");
                valueString = String.Format("values (LAST_INSERT_ID(), '{0}')", comment);

                string nonQueryString2 = String.Format("{0} {1} {2};", insertIntoString, columnString, valueString);
                string nonQueryString = String.Format("{0} {1}", nonQueryString1, nonQueryString2);
                NonQuery(nonQueryString.ToString());

            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return;
            }
        }

        /// <summary>
        /// Verifies that the provided logCode is correct, compared to database.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <param name="logCode">Code to be verified.</param>
        /// <returns>True if found and unique.</returns>
        private bool VerifyLogCodeExists(string logCode)
        {
            MySqlDataReader result = null;
            try
            {
                string selectString = String.Format("select count(code)");
                string fromString = String.Format("from `logcodes`");
                string whereString = String.Format("where code = '{0}'", logCode);

                string queryString = String.Format("{0} {1} {2}", selectString, fromString, whereString);
                result = Query(queryString.ToString());
                
                if (result == null)
                {
                    result.Close();
                    return false;
                }

                result.Read();
                if (!result.HasRows)
                {
                    result.Close();
                    return false;
                }

                if (result.GetInt16(0) == 1)
                {
                    result.Close();
                    return true;
                }
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
            finally
            {
                result.Close();
            }

            return false;
        }
        #endregion

        #region Key
        /// <summary>
        /// Deletes the key from the database.
        /// </summary>
        /// <param name="key">The key name to be deleted</param>
        /// <returns>True on success.</returns>
        public bool DeleteKey(string key)
        {
            // Verify category exists
            if (!VerifyKeyExists(key))
            {
                Popup.Error("Den nyckel du försöker radera existerar inte?");
                return false;
            }

            // Build nonQueryString
            try
            {
                string insertIntoString = String.Format("delete from `key`");
                string whereString = String.Format("where name = '{0}'", key);

                string nonQueryString = String.Format("{0} {1}", insertIntoString, whereString);
                if (NonQuery(nonQueryString.ToString()) == 1)
                    return true;
                else
                    throw new IndexOutOfRangeException("Indexfel i databas.");;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
        }

        /// <summary>
        /// Deletes the key from the database.
        /// </summary>
        /// <param name="key">The key name to be deleted</param>
        /// <returns>True on success.</returns>
        public bool DeleteKey(Model.Key key)
        {
            return DeleteKey(key.Name);
        }

        /// <summary>
        /// Search for a key with provided name and returns this as a Key object.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <param name="name">The name of the key</param>
        /// <returns>Key from database</returns>
        public Model.Key GetKeyFromName(string name)
        {
            MySqlDataReader result = null;
            try
            {
                string selectString = String.Format("select name, user, updated");
                string fromString = String.Format("from `key`");
                string whereString = String.Format("where name = '{0}'", name);

                string queryString = String.Format("{0} {1} {2}", selectString, fromString, whereString);
                result = Query(queryString.ToString());

                if (result == null)
                {
                    result.Close();
                    return null;
                }

                result.Read();
                if (!result.HasRows)
                {
                    result.Close();
                    return null;
                }

                //Extract Key
                Model.Key key = new Model.Key();
                key.Name = result.GetString(0);
                key.UserInitials = result.GetString(1);
                if (!result.IsDBNull(2))
                    key.Updated = result.GetDateTime(2);
                else
                    key.Updated = DateTime.MinValue;

                result.Close();
                return key;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return null;
            }
            finally
            {
                result.Close();
            }
        }

        /// <summary>
        /// Returns all keys in the database as a datatable of strings.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <returns>A datatable of strings.</returns>
        public DataTable GetAllKeysAsDataTable()
        {
            // Create datatable:
            DataTable table = new DataTable();
            table.Columns.Add("Namn", typeof(string));
            table.Columns.Add("Utdelad till", typeof(string));
            table.Columns.Add("Uppdaterad", typeof(DateTime));

            // Create Query:
            MySqlDataReader result = null;
            try
            {
                string selectString = String.Format("select name, user, updated");
                string fromString = String.Format("from `key`");
                string queryString = String.Format("{0} {1}", selectString, fromString);
                result = Query(queryString.ToString());

                while (result.Read())
                {
                    //Extract Key
                    Model.Key key= new Model.Key();
                    key.Name = result.GetString(0);
                    key.UserInitials = result.GetString(1);
                    if (!result.IsDBNull(2))
                        key.Updated = result.GetDateTime(2);
                    else
                        key.Updated = DateTime.MinValue;
                    table.Rows.Add(key.Name, key.UserInitials, key.Updated);
                }

            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return null;
            }
            finally
            {
                result.Close();
            }
            return table;
        }

        /// <summary>
        /// Verifies that there is a key with the name provided
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <param name="keyName">Name to be verified</param>
        /// <returns>True if exists and unique.</returns>
        private bool VerifyKeyExists(string keyName)
        {
            MySqlDataReader result = null;
            try
            {
                string selectString = String.Format("select count(name)");
                string fromString = String.Format("from `key`");
                string whereString = String.Format("where name = '{0}'", keyName);

                string queryString = String.Format("{0} {1} {2}", selectString, fromString, whereString);
                result = Query(queryString.ToString());

                if (result == null)
                {
                    result.Close();
                    return false;
                }

                result.Read();
                if (!result.HasRows)
                {
                    result.Close();
                    return false;
                }

                if (result.GetInt16(0) == 1)
                {
                    result.Close();
                    return true;
                }
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
            finally
            {
                result.Close();
            }

            return false;
        }

        /// <summary>
        /// Updates the key with the name provided in the key object in the database.
        /// Note that the name is supposed to be read only and won't be changed.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// - Note that the name is supposed to be read only and won't be changed.
        /// </remarks>
        /// <param name="key">The information to be set.</param>
        /// <returns>True on success.</returns>
        public bool UpdateKey(Model.Key key)
        {
            // First: Verify that the name exists:
            if (!VerifyKeyExists(key.Name))
            {
                Popup.Information("Det finns ingen nyckel med namnet " + key.Name, "Fel");
                return false;
            }

            if (!VerifyUserInitialsExists(key.UserInitials))
            {
                Popup.Information("Det finns ingen användare med initialerna " + key.UserInitials, "Fel");
                return false;
            }

            // Second: Build nonQueryString
            try
            {
                string updateString = String.Format("update `key`");
                string setString = String.Format("set user = '{0}'", key.UserInitials);
                string whereString = String.Format("where name = '{0}'", key.Name);

                string nonQueryString = String.Format("{0} {1} {2}", updateString, setString, whereString);
                if (NonQuery(nonQueryString.ToString()) == 1)
                    return true;
                else
                    throw new IndexOutOfRangeException("Indexfel i databas.");;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
        }

        /// <summary>
        /// Saves a new key to the database.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <param name="key">The key to be saved.</param>
        /// <returns>True on success.</returns>
        public bool SaveNewKey(Model.Key key)
        {
            // First: Verify that the name NOT exists:
            if (VerifyKeyExists(key.Name))
            {
                Popup.Information("Det finns redan en nyckel med namnet " + key.Name + "\nNamnet måste vara unikt.", "Kunde inte spara!");
                return false;
            }

            if (!VerifyUserInitialsExists(key.UserInitials))
            {
                Popup.Information("Det finns ingen användare med initialerna " + key.UserInitials, "Kunde inte spara!");
                return false;
            }

            // Build nonQueryString
            try
            {
                string insertIntoString = String.Format("insert into `key`");
                string columnString = String.Format("(name, user)");
                string valueString = String.Format("values ('{0}', '{1}')", key.Name, key.UserInitials);

                string nonQueryString = String.Format("{0} {1} {2}", insertIntoString, columnString, valueString);
                if (NonQuery(nonQueryString.ToString()) == 1)
                    return true;
                else
                    throw new IndexOutOfRangeException("Indexfel i databas.");;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
        }
        #endregion

        #region StockLocation
        #region StockLocationArea
        /// <summary>
        /// Deletes the area, and all location associated with it from the database.
        /// </summary>
        /// <param name="area">The area to be deleted</param>
        /// <returns>True on success</returns>
        public bool DeleteStockLocationArea(string area)
        {
            // Verify category exists
            if (!VerifyStockLocationAreaExists(area))
            {
                Popup.Error("Det området du försöker radera existerar inte?");
                return false;
            }

            // Build nonQueryString
            try
            {
                string insertIntoString = String.Format("delete from `stocklocationarea`");
                string whereString = String.Format("where area = '{0}'", area);

                string nonQueryString = String.Format("{0} {1}", insertIntoString, whereString);
                if (NonQuery(nonQueryString.ToString()) == 1)
                    return true;
                else
                    throw new IndexOutOfRangeException("Indexfel i databas.");;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
        }

        /// <summary>
        /// Deletes the area, and all location associated with it from the database.
        /// </summary>
        /// <param name="area">The area to be deleted</param>
        /// <returns>True on success.</returns>
        public bool DeleteStockLocationArea(Model.StockLocationArea area)
        {
            return DeleteStockLocationArea(area.Area);
        }

        /// <summary>
        /// Gets current count of StockLocationAreas in database.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <returns>The number of areas</returns>
        public int GetStockLocationAreaCount()
        {
            MySqlDataReader result = null;
            int count = 0;
            try
            {
                string selectString = String.Format("select count(area)");
                string fromString = String.Format("from `stocklocationarea`");

                string queryString = String.Format("{0} {1}", selectString, fromString);
                result = Query(queryString.ToString());
                result.Read();
                count = result.GetInt16(0);
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return 0;
            }
            finally
            {
                result.Close();
            }

            return count;
        }

        /// <summary>
        /// Gets the area as an object from the area code.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <param name="area">The area code. Usually two characters.</param>
        /// <returns>The area for the provided area code</returns>
        public Model.StockLocationArea GetStockLocationAreaFromArea(string area)
        {
            MySqlDataReader result = null;
            try
            {
                string selectString = String.Format("select area, definition");
                string fromString = String.Format("from `stocklocationarea`");
                string whereString = String.Format("where area = '{0}'", area);

                string queryString = String.Format("{0} {1} {2}", selectString, fromString, whereString);
                result = Query(queryString.ToString());

                if (result == null)
                {
                    result.Close();
                    return null;
                }

                result.Read();
                if (!result.HasRows)
                {
                    result.Close();
                    return null;
                }

                //Extract area
                Model.StockLocationArea areaObject = new Model.StockLocationArea();
                areaObject.Area = result.GetString("area");
                areaObject.Definition = result.GetString("definition");
                result.Close();
                return areaObject;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return null;
            }
            finally
            {
                result.Close();
            }
        }
        public Model.StockLocationArea GetStockLocationAreaFromDefinition(string areaDefinition)
        {
            MySqlDataReader result = null;
            try
            {
                string selectString = String.Format("select area, definition");
                string fromString = String.Format("from `stocklocationarea`");
                string whereString = String.Format("where definition = '{0}'", areaDefinition);

                string queryString = String.Format("{0} {1} {2}", selectString, fromString, whereString);
                result = Query(queryString.ToString());

                if (result == null)
                {
                    result.Close();
                    return null;
                }

                result.Read();
                if (!result.HasRows)
                {
                    result.Close();
                    return null;
                }

                //Extract area
                Model.StockLocationArea areaObject = new Model.StockLocationArea();
                areaObject.Area = result.GetString("area");
                areaObject.Definition = result.GetString("definition");
                result.Close();
                return areaObject;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return null;
            }
            finally
            {
                result.Close();
            }
        }

        /// <summary>
        /// Returns all the stocklocationareas as an array.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <returns>Array of all areas</returns>
        public Model.StockLocationArea[] GetStockLocationAreasAsArray()
        {
            int count = GetStockLocationAreaCount();
            Model.StockLocationArea[] areas = new Model.StockLocationArea[count];

            // Create Query:
            MySqlDataReader result = null;
            try
            {
                string selectString = String.Format("select area, definition");
                string fromString = String.Format("from `stocklocationarea`");
                string queryString = String.Format("{0} {1}", selectString, fromString);
                result = Query(queryString.ToString());
                int index = 0;
                while (result.Read())
                {
                    Model.StockLocationArea area = new Model.StockLocationArea();
                    area.Area = result.GetString("area");
                    area.Definition = result.GetString("definition");
                    areas[index] = area;
                    index++;
                }
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return null;
            }
            finally
            {
                result.Close();
            }
            return areas;
        }

        /// <summary>
        /// Returns all the stock locationareas as a datatable.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <returns>Datatable with all areas</returns>
        public DataTable GetStockLocationAreasAsDataTable()
        {
            // Create datatable:
            DataTable table = new DataTable();
            table.Columns.Add("Område", typeof(string));
            table.Columns.Add("Beskrivning", typeof(string));

            Model.StockLocationArea[] areasArray = GetStockLocationAreasAsArray();
            if (areasArray == null)
                return null;
            int index = 0;

            while (index < areasArray.Length)
            {
                table.Rows.Add(areasArray[index].Area, areasArray[index].Definition);
                index++;
            }

            return table;
        }

        /// <summary>
        /// Saves StockLocationArea to database.
        /// Note that id is not saved.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// - Note that id is not saved.
        /// </remarks>
        /// <param name="area">The area to be saved.</param>
        /// <returns></returns>
        public bool SaveNewStockLocationArea(Model.StockLocationArea area)
        {
            // First: Verify that the area NOT exists:
            if (VerifyStockLocationAreaExists(area.Area))
            {
                Popup.Information("Det finns redan ett lagerområde med koden " + area.Area + "\nKoden måste vara unik.", "Kunde inte spara!");
                return false;
            }

            // Build nonQueryString
            try
            {
                string insertIntoString = String.Format("insert into `stocklocationarea`");
                string columnString = String.Format("(area, definition)");
                string valueString = String.Format("values ('{0}', '{1}')", area.Area, area.Definition);

                string nonQueryString = String.Format("{0} {1} {2}", insertIntoString, columnString, valueString);
                if (NonQuery(nonQueryString.ToString()) == 1)
                    return true;
                else
                    throw new IndexOutOfRangeException("Indexfel i databas.");;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
        }

        /// <summary>
        /// Updates the corresponding StockLocationArea in the database.
        /// Note that the area code cannot be updated.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection
        /// - Note that areacode must exist.
        /// </remarks>
        /// <param name="area">The area to be updated</param>
        /// <returns>True on success.</returns>
        public bool UpdateStockLocationArea(Model.StockLocationArea area)
        {
            // First: Verify that the area exists:
            if (!VerifyStockLocationAreaExists(area.Area))
            {
                Popup.Information("Det finns inget lagerområde med koden " + area.Area, "Fel");
                return false;
            }

            // Second: Build nonQueryString
            try
            {
                string updateString = String.Format("update `stocklocationarea`");
                string setString = String.Format("set definition = '{0}'", area.Definition);
                string whereString = String.Format("where area = '{0}'", area.Area);

                string nonQueryString = String.Format("{0} {1} {2}", updateString, setString, whereString);
                if (NonQuery(nonQueryString.ToString()) == 1)
                    return true;
                else
                    throw new IndexOutOfRangeException("Indexfel i databas.");;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
        }

        /// <summary>
        /// Verifies that the provided stock location area exists.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <param name="areaCode">The area code to be checked.</param>
        /// <returns>True if found and unique.</returns>
        public bool VerifyStockLocationAreaExists(string areaCode)
        {
            MySqlDataReader result = null;
            try
            {
                string selectString = String.Format("select count(area)");
                string fromString = String.Format("from `stocklocationarea`");
                string whereString = String.Format("where area = '{0}'", areaCode);

                string queryString = String.Format("{0} {1} {2}", selectString, fromString, whereString);
                result = Query(queryString.ToString());

                if (result == null)
                {
                    result.Close();
                    return false;
                }

                result.Read();
                if (!result.HasRows)
                {
                    result.Close();
                    return false;
                }

                if (result.GetInt16(0) == 1)
                {
                    result.Close();
                    return true;
                }
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
            finally
            {
                result.Close();
            }

            return false;
        }

        #endregion

        /// <summary>
        /// Deletes the location from the database.
        /// </summary>
        /// <param name="location">The location to be deleted</param>
        /// <returns>True on success.</returns>
        public bool DeleteStockLocation(int location)
        {
            // Verify category exists
            if (!VerifyStockLocationExists(location))
            {
                Popup.Error("Den lagerplats du försöker radera existerar inte?");
                return false;
            }

            // Build nonQueryString
            try
            {
                string insertIntoString = String.Format("delete from `stocklocation`");
                string whereString = String.Format("where id = {0}", location);

                string nonQueryString = String.Format("{0} {1}", insertIntoString, whereString);
                if (NonQuery(nonQueryString.ToString()) == 1)
                    return true;
                else
                    throw new IndexOutOfRangeException("Indexfel i databas.");;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
        }

        /// <summary>
        /// Deletes the location from the database.
        /// </summary>
        /// <param name="location">The location to be deleted</param>
        /// <returns>True on success.</returns>
        public bool DeleteStockLocationArea(Model.StockLocation location)
        {
            return DeleteStockLocation(location.Id);
        }

        /// <summary>
        /// Get the number of stock location in the database.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <returns>Number of stock location in database</returns>
        public int GetStockLocationCount()
        {
            MySqlDataReader result = null;
            int count = 0;
            try
            {
                string selectString = String.Format("select count(id)");
                string fromString = String.Format("from `stocklocation`");

                string queryString = String.Format("{0} {1}", selectString, fromString);
                result = Query(queryString.ToString());
                result.Read();
                count = result.GetInt16(0);
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return 0;
            }
            finally
            {
                result.Close();
            }

            return count;
        }

        /// <summary>
        /// Returns the stock location that correspond to given id.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <param name="id">The id of the stock location</param>
        /// <returns>The stockLocation corresponding to provided id.</returns>
        public Model.StockLocation GetStockLocationFromId(int id)
        {
            MySqlDataReader result = null;
            try
            {
                string selectString = String.Format("select id, area, shelf, level, section");
                string fromString = String.Format("from `stocklocation`");
                string whereString = String.Format("where id = {0}", id);

                string queryString = String.Format("{0} {1} {2}", selectString, fromString, whereString);
                result = Query(queryString.ToString());

                if (result == null)
                {
                    result.Close();
                    return null;
                }

                result.Read();
                if (!result.HasRows)
                {
                    result.Close();
                    return null;
                }

                //Extract area
                Model.StockLocation location = new Model.StockLocation();
                string areaString = result.GetString("area");
                location.Id = result.GetInt32("id");
                location.Shelf = result.GetString("shelf");
                location.Level = result.GetString("level");
                location.Section = result.GetString("section");
                
                result.Close();
                location.Area = GetStockLocationAreaFromArea(areaString);

                return location;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return null;
            }
            finally
            {
                result.Close();
            }
        }

        public Model.StockLocation GetStockLocationFromAreaShelfLevelSection(string areaDefinition, string shelf, string level, string section)
        {
            Model.StockLocationArea area = GetStockLocationAreaFromDefinition(areaDefinition);
            MySqlDataReader result = null;
            try
            {
                string selectString = String.Format("select id, shelf, level, section");
                string fromString = String.Format("from `stocklocation`");
                string whereString = String.Format("where area = '{0}' and shelf = '{1}' and level = '{2}' and section = '{3}'", area.Area, shelf
                    , level, section);

                string queryString = String.Format("{0} {1} {2}", selectString, fromString, whereString);
                result = Query(queryString.ToString());

                if (result == null)
                {
                    result.Close();
                    return null;
                }

                result.Read();
                if (!result.HasRows)
                {
                    result.Close();
                    return null;
                }

                //Extract area
                Model.StockLocation location = new Model.StockLocation();
                location.Area = area;
                location.AreaCode = area.Area;
                location.Id = result.GetInt32("id");
                location.Shelf = result.GetString("shelf");
                location.Level = result.GetString("level");
                location.Section = result.GetString("section");

                result.Close();

                return location;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return null;
            }
            finally
            {
                result.Close();
            }
        }

        /// <summary>
        /// Returns all the stock location in the database as an array.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <returns>Array of all stock locations.</returns>
        public Model.StockLocation[] GetStockLocationAsArray()
        {
            int count = GetStockLocationCount();
            Model.StockLocation[] locations = new Model.StockLocation[count];

            // Create Query:
            MySqlDataReader result = null;
            try
            {
                string selectString = String.Format("select id, area, shelf, level, section");
                string fromString = String.Format("from `stocklocation`");
                string queryString = String.Format("{0} {1}", selectString, fromString);
                result = Query(queryString.ToString());
                int index = 0;
                while (result.Read())
                {
                    Model.StockLocation location = new Model.StockLocation();
                    //location.Area = GetStockLocationAreaFromArea(result.GetString("area"));
                    location.AreaCode = result.GetString("area");
                    location.Id = result.GetInt32("id");
                    location.Shelf = result.GetString("shelf");
                    location.Level = result.GetString("level");
                    location.Section = result.GetString("section");
                    locations[index] = location;
                    index++;
                }
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return null;
            }
            finally
            {
                result.Close();
            }

            //Now we can extract area from code:
            int i = 0;
            while(i<locations.Length)
            {
                locations[i].Area = GetStockLocationAreaFromArea(locations[i].AreaCode);
                i++;
            }
            return locations;
        }

        /// <summary>
        /// Returns datatable with all the stock locations in the database.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <returns>Datatable with all stock locations in the database as strings.</returns>
        public DataTable GetStockLocationAsDataTable()
        {
            // Create datatable:
            DataTable table = new DataTable();
            table.Columns.Add("Id", typeof(string));
            table.Columns.Add("Område", typeof(string));
            table.Columns.Add("Hylla", typeof(string));
            table.Columns.Add("Våning", typeof(string));
            table.Columns.Add("Sektion", typeof(string));

            Model.StockLocation[] locationArray = GetStockLocationAsArray();
            if (locationArray == null)
                return null;
            int index = 0;

            while (index < locationArray.Length)
            {
                table.Rows.Add(locationArray[index].Id.ToString(), locationArray[index].Area.Definition, locationArray[index].Shelf, locationArray[index].Level, locationArray[index].Section);
                index++;
            }

            return table;
        }

        /// <summary>
        /// Saves the given stock location to the database.
        /// Takes no notice of the id.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// - Takes no notice of the id.
        /// </remarks>
        /// <param name="location">The location to be saved</param>
        /// <returns>True on success.</returns>
        public bool SaveNewStockLocation(Model.StockLocation location)
        {
            // First: Verify that the area NOT exists:
            if (VerifyStockLocationExists(location))
            {
                Popup.Information("Det finns redan en lagerplats med koden " + location.StockLocationCode + "\nKoden måste vara unik.", "Kunde inte spara!");
                return false;
            }

            // Build nonQueryString
            try
            {
                string insertIntoString = String.Format("insert into `stocklocation`");
                string columnString = String.Format("(area, shelf, level, section)");
                string valueString = String.Format("values ('{0}', '{1}', '{2}', '{3}')", location.Area.Area, location.Shelf, location.Level, location.Section);

                string nonQueryString = String.Format("{0} {1} {2}", insertIntoString, columnString, valueString);
                if (NonQuery(nonQueryString.ToString()) == 1)
                    return true;
                else
                    throw new IndexOutOfRangeException("Indexfel i databas.");;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
        }

        /// <summary>
        /// Updates the database for given stock location.
        /// Reads the id and match against database.
        /// Id must be in database.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <param name="location">The location to be updated.s</param>
        /// <returns>True on success.</returns>
        public bool UpdateStockLocation(Model.StockLocation location)
        {
            // First: Verify that the ID exists:
            if (!VerifyStockLocationExists(location.Id))
            {
                Popup.Information("Det finns ingen lagerplats med koden " + location.Id.ToString(), "Fel");
                return false;
            }
            // First: Verify that the unique LOCATION NOT exists:
            if (VerifyStockLocationExists(location))
            {
                Popup.Information("Det finns redan en lagerplats med koden " + location.StockLocationCode + "\nKoden måste vara unik.", "Kunde inte spara!");
                return false;
            }

            // Second: Build nonQueryString
            try
            {
                string updateString = String.Format("update `stocklocation`");
                string setString = String.Format("set area = '{0}', shelf = '{1}', level = '{2}', section = '{3}'", location.Area.Area, location.Shelf, location.Level, location.Section);
                string whereString = String.Format("where id = '{0}'", location.Id);

                string nonQueryString = String.Format("{0} {1} {2}", updateString, setString, whereString);
                if (NonQuery(nonQueryString.ToString()) == 1)
                    return true;
                else
                    throw new IndexOutOfRangeException("Indexfel i databas.");;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
        }

        /// <summary>
        /// Checks if there is a location with that id.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <param name="id">The id to be verified</param>
        /// <returns>True if found and unique.</returns>
        public bool VerifyStockLocationExists(int id)
        {
            MySqlDataReader result = null;
            try
            {
                string selectString = String.Format("select count(id)");
                string fromString = String.Format("from `stocklocation`");
                string whereString = String.Format("where id = {0}", id);

                string queryString = String.Format("{0} {1} {2}", selectString, fromString, whereString);
                result = Query(queryString.ToString());

                if (result == null)
                {
                    result.Close();
                    return false;
                }

                result.Read();
                if (!result.HasRows)
                {
                    result.Close();
                    return false;
                }

                if (result.GetInt16(0) == 1)
                {
                    result.Close();
                    return true;
                }
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
            finally
            {
                result.Close();
            }

            return false;
        }

        /// <summary>
        /// Checks if there is a identical location in the database.
        /// Note: does not compare id.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// - Does not compare id.
        /// </remarks>
        /// <param name="location">The location to be checked for doubles</param>
        /// <returns>True if double is found</returns>
        public bool VerifyStockLocationExists(Model.StockLocation location)
        {
            MySqlDataReader result = null;
            try
            {
                string selectString = String.Format("select count(id)");
                string fromString = String.Format("from `stocklocation`");
                string whereString = String.Format("where area = '{0}' and shelf = '{1}' and level = '{2}' and section = '{3}'", location.Area.Area, location.Shelf, location.Level, location.Section);

                string queryString = String.Format("{0} {1} {2}", selectString, fromString, whereString);
                result = Query(queryString.ToString());

                if (result == null)
                {
                    result.Close();
                    return false;
                }

                result.Read();
                if (!result.HasRows)
                {
                    result.Close();
                    return false;
                }

                if (result.GetInt16(0) == 1)
                {
                    result.Close();
                    return true;
                }
                
                if (result.GetInt16(0) == 0)
                {
                    result.Close();
                    return false;
                }
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
            finally
            {
                result.Close();
            }

            return false;
        }

        public string[] GetStockLocationAreaArray()
        {
            List<string> types = new List<string>();

            // Create Query:
            MySqlDataReader result = null;

            try
            {
                string selectString = String.Format("select definition");
                string fromString = String.Format("from `stocklocationarea`");
                string queryString = String.Format("{0} {1}", selectString, fromString);
                result = Query(queryString.ToString());

                while (result.Read())
                    types.Add(result.GetString("definition"));
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return null;
            }
            finally
            {
                result.Close();
            }

            string[] returnArray = new string[types.Count];
            int i = 0;
            while (i < types.Count)
            {
                returnArray[i] = types[i];
                i++;
            }
            return returnArray;
        }

        public string[] GetStockLocationShelfArray()
        {
            List<string> types = new List<string>();

            // Create Query:
            MySqlDataReader result = null;

            try
            {
                string selectString = String.Format("select distinct(shelf)");
                string fromString = String.Format("from `stocklocation`");
                string queryString = String.Format("{0} {1}", selectString, fromString);
                result = Query(queryString.ToString());

                while (result.Read())
                    types.Add(result.GetString("shelf"));
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return null;
            }
            finally
            {
                result.Close();
            }

            string[] returnArray = new string[types.Count];
            int i = 0;
            while (i < types.Count)
            {
                returnArray[i] = types[i];
                i++;
            }
            return returnArray;
        }

        public string[] GetStockLocationLevelArray()
        {
            List<string> types = new List<string>();

            // Create Query:
            MySqlDataReader result = null;

            try
            {
                string selectString = String.Format("select distinct(level)");
                string fromString = String.Format("from `stocklocation`");
                string queryString = String.Format("{0} {1}", selectString, fromString);
                result = Query(queryString.ToString());

                while (result.Read())
                    types.Add(result.GetString("level"));
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return null;
            }
            finally
            {
                result.Close();
            }

            string[] returnArray = new string[types.Count];
            int i = 0;
            while (i < types.Count)
            {
                returnArray[i] = types[i];
                i++;
            }
            return returnArray;
        }

        public string[] GetStockLocationSectionArray()
        {
            List<string> types = new List<string>();

            // Create Query:
            MySqlDataReader result = null;

            try
            {
                string selectString = String.Format("select distinct(section)");
                string fromString = String.Format("from `stocklocation`");
                string queryString = String.Format("{0} {1}", selectString, fromString);
                result = Query(queryString.ToString());

                while (result.Read())
                    types.Add(result.GetString("section"));
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return null;
            }
            finally
            {
                result.Close();
            }

            string[] returnArray = new string[types.Count];
            int i = 0;
            while (i < types.Count)
            {
                returnArray[i] = types[i];
                i++;
            }
            return returnArray;
        }

        #endregion

        #region ArticleCategory
        /// <summary>
        /// Returns the category for given id
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <param name="id">The id of an category</param>
        /// <returns>Category as an object</returns>
        public Model.ArticleCategory GetArticleCategoryFromId(int id)
        {
            MySqlDataReader result = null;
            string name;
            int main;

            try
            {
                string selectString = String.Format("select name, mainCategory");
                string fromString = String.Format("from `articlecategory`");
                string whereString = String.Format("where id = '{0}'", id);

                string queryString = String.Format("{0} {1} {2}", selectString, fromString, whereString);
                result = Query(queryString.ToString());

                if (result == null)
                {
                    result.Close();
                    return null;
                }

                result.Read();
                if (!result.HasRows)
                {
                    result.Close();
                    return null;
                }

                //Extract ArticleCategory as string (we need to close result to lookup main)
               name = result.GetString(0);
               if (!result.IsDBNull(1))
                   main = result.GetInt32(1);
               else
                   main = 0;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return null;
            }
            finally
            {
                result.Close();
            }
            if (main == 0)
                return new Model.ArticleCategory(id, name, 0);
            else
                return new Model.ArticleCategory(id, name, main);
        }

        /// <summary>
        /// Returns the category name from given id
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <param name="id">Id to be converted to name</param>
        /// <returns>Name for given id</returns>
        public string GetArticleCategoryNameFromId(int id)
        {
            MySqlDataReader result = null;
            string name;

            try
            {
                string selectString = String.Format("select name");
                string fromString = String.Format("from `articlecategory`");
                string whereString = String.Format("where id = '{0}'", id);

                string queryString = String.Format("{0} {1} {2}", selectString, fromString, whereString);
                result = Query(queryString.ToString());

                if (result == null)
                {
                    result.Close();
                    return null;
                }

                result.Read();
                if (!result.HasRows)
                {
                    result.Close();
                    return null;
                }

                name = result.GetString(0);
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return null;
            }
            finally
            {
                result.Close();
            }
            return name;
        }

        /// <summary>
        /// Returns all the categories in the database as an array.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <returns>Array of categories.</returns>
        public Model.ArticleCategory[] GetArticleCategoriesAsArray()
        {
            int count = GetArticleCategoryCount();
            Model.ArticleCategory[] categories = new Model.ArticleCategory[count];
            int[] ids = new int[count];

            // Create Query:
            MySqlDataReader result = null;
            
            try
            {
                string selectString = String.Format("select id");
                string fromString = String.Format("from `articlecategory`");
                string queryString = String.Format("{0} {1}", selectString, fromString);
                result = Query(queryString.ToString());
                int index = 0;
                while (result.Read())
                {
                    ids[index] = result.GetInt32("id");
                    index++;
                }
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return null;
            }
            finally
            {
                result.Close();
            }

            //Now we can extract area from code:
            int i = 0;
            while (i < ids.Length)
            {
                categories[i] = GetArticleCategoryFromId(ids[i]);
                i++;
            }
            return categories;
        }

        /// <summary>
        /// Returns a list of all categories in database.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <returns>List of all categories</returns>
        public List<Model.ArticleCategory> GetArticleCategoriesAsList()
        {
            List<Model.ArticleCategory> items = new List<Model.ArticleCategory>();
            Model.ArticleCategory[] array = GetArticleCategoriesAsArray();
            if (array == null)
                return null;
            int i = 0;
            while (i < array.Length)
            {
                items.Add(array[i]);
                i++;
            }

            return items;
        }

        /// <summary>
        /// Returns all the categories as a datatable
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <returns>Datatable with all the categories</returns>
        public DataTable GetArticleCategoriesAsDataTable()
        {
            // Create datatable:
            DataTable table = new DataTable();
            table.Columns.Add("Id", typeof(string));
            table.Columns.Add("Namn", typeof(string));
            table.Columns.Add("Överordnad", typeof(string));

            Model.ArticleCategory[] categories = GetArticleCategoriesAsArray();
            if (categories == null)
                return null;
            int index = 0;

            while (index < categories.Length)
            {
                table.Rows.Add(categories[index].Id.ToString(), categories[index].Name, categories[index].MainCategory.ToString());
                index++;
            }

            return table;
        }

        /// <summary>
        /// Saves the given category to database.
        /// Note that the category id is not used
        /// Note that the category must be unique.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <param name="category">The category to be saved</param>
        /// <returns>True on success.</returns>
        public bool SaveNewArticleCategory(Model.ArticleCategory category)
        {
            // First: Verify that the identical NOT exists:
            if (category.HasMainCategory)
            {
                if (VerifyArticleCategoryDoubleExists(category.Name, category.MainCategory))
                {
                    Popup.Information("Det finns redan en identisk kategori.", "Kunde inte spara!");
                    return false;
                }
            }
            else
            {
                if (VerifyArticleCategoryDoubleExists(category.Name, 0))
                {
                    Popup.Information("Det finns redan en identisk kategori.", "Kunde inte spara!");
                    return false;
                } 
            }

            // Verify main category exists
            if (category.HasMainCategory)
                if (!VerifyArticleCategoryExists(category.MainCategory))
                    Popup.Error("Den kategori som är markerad som överordnad kategori existerar inte.");


            // Build nonQueryString
            try
            {
                string insertIntoString = String.Format("insert into `articlecategory`");
                string columnString;
                string valueString;
                if(category.HasMainCategory)
                {
                    columnString = String.Format("(name, mainCategory)");
                    valueString = String.Format("values ('{0}', '{1}')", category.Name, category.MainCategory);
                }
                else
                {
                    columnString = String.Format("(name)");
                    valueString = String.Format("values ('{0}')", category.Name);
                }

                string nonQueryString = String.Format("{0} {1} {2}", insertIntoString, columnString, valueString);
                if (NonQuery(nonQueryString.ToString()) == 1)
                    return true;
                else
                    throw new IndexOutOfRangeException("Indexfel i databas.");;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
        }

        /// <summary>
        /// Updates the database with given category.
        /// Note that the category id must correspond to id in database.
        /// Note that the category must be unique.
        /// Note that the category cant have is self as main.
        /// </summary>
        /// <param name="category">Category to be updated in the database</param>
        /// <returns>True on succes.</returns>
        public bool UpdateArticleCategory(Model.ArticleCategory category)
        {
            // First: Verify that the id exists:
            if (!VerifyArticleCategoryExists(category.Id))
            {
                Popup.Information("Eftersom det inte finns någon kategori med detta id-nummer så kunder vi inte uppdatera.", "Fel");
                return false;
            }

            // Second: Verify that the identical NOT exists:
            if (category.HasMainCategory)
            {
                if (VerifyArticleCategoryDoubleExists(category.Name, category.MainCategory))
                {
                    Popup.Information("Det finns redan en identisk kategori.", "Kunde inte spara!");
                    return false;
                }
            }
            else
            {
                if (VerifyArticleCategoryDoubleExists(category.Name, 0))
                {
                    Popup.Information("Det finns redan en identisk kategori.", "Kunde inte spara!");
                    return false;
                }
            }

            //third: check that main is not self
            if (category.Id == category.MainCategory)
            {
                Popup.Error("En kategori kan inte vara underkategori till sig själv.");
                return false;
            }

            // Fourth: Build nonQueryString
            try
            {
                string updateString = String.Format("update `articlecategory`");
                string setString;
                string whereString;
                if (category.HasMainCategory)
                {
                    setString = String.Format("set name = '{0}', mainCategory = {1}", category.Name, category.MainCategory);
                    whereString = String.Format("where id = '{0}'", category.Id);
                }
                else
                {
                    setString = String.Format("set name = '{0}', mainCategory = null", category.Name);
                    whereString = String.Format("where id = '{0}'", category.Id);
                }

                string nonQueryString = String.Format("{0} {1} {2}", updateString, setString, whereString);
                if (NonQuery(nonQueryString.ToString()) == 1)
                    return true;
                else
                    throw new IndexOutOfRangeException("Indexfel i databas.");;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
        }

        /// <summary>
        /// Deletes a category from the database.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <param name="id">The id of category to be deleted.</param>
        /// <returns>True on success.</returns>
        public bool DeleteArticleCategory(int id)
        {
            // Verify category exists
            if (!VerifyArticleCategoryExists(id))
            {
                Popup.Error("Den kategori du försöker radera existerar inte?");
                return false;
            }

            // Build nonQueryString
            try
            {
                string insertIntoString = String.Format("delete from `articlecategory`");
                string whereString = String.Format("where id = {0}", id);

                string nonQueryString = String.Format("{0} {1}", insertIntoString, whereString);
                if (NonQuery(nonQueryString.ToString()) == 1)
                    return true;
                else
                    throw new IndexOutOfRangeException("Indexfel i databas.");;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
        }

        /// <summary>
        /// Deletes a category from the database.
        /// Reads only the id from parameter.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <param name="id">The id of category to be deleted.</param>
        /// <returns>True on success.</returns>
        public bool DeleteArticleCategory(Model.ArticleCategory category)
        {
            return DeleteArticleCategory(category.Id);
        }

        /// <summary>
        /// Verifies that there is a category with provided id
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <param name="id">Id to be verified</param>
        /// <returns>True on success.</returns>
        public bool VerifyArticleCategoryExists(int id)
        {
            MySqlDataReader result = null;
            try
            {
                string selectString = String.Format("select count(id)");
                string fromString = String.Format("from `articlecategory`");
                string whereString = String.Format("where id = '{0}'", id);

                string queryString = String.Format("{0} {1} {2}", selectString, fromString, whereString);
                result = Query(queryString.ToString());

                if (result == null)
                {
                    result.Close();
                    return false;
                }

                result.Read();
                if (!result.HasRows)
                {
                    result.Close();
                    return false;
                }

                if (result.GetInt16(0) == 1)
                {
                    result.Close();
                    return true;
                }
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
            finally
            {
                result.Close();
            }

            return false;
        }

        /// <summary>
        /// Checks for doubles for the given parameters together.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <param name="name">Name to be checked</param>
        /// <param name="mainCategory">Main cat to be checked</param>
        /// <returns>True if double is found.</returns>
        public bool VerifyArticleCategoryDoubleExists(string name, int mainCategory)
        {
            MySqlDataReader result = null;
            try
            {
                string selectString = String.Format("select count(id)");
                string fromString = String.Format("from `articlecategory`");
                string whereString;
                if(mainCategory != 0)
                    whereString = String.Format("where name = '{0}' and mainCategory = {1}", name, mainCategory);
                else
                    whereString = String.Format("where name = '{0}' and mainCategory is null", name);

                string queryString = String.Format("{0} {1} {2}", selectString, fromString, whereString);
                result = Query(queryString.ToString());

                if (result == null)
                {
                    result.Close();
                    return false;
                }

                result.Read();
                if (!result.HasRows)
                {
                    result.Close();
                    return false;
                }

                if (result.GetInt16(0) == 1)
                {
                    result.Close();
                    return true;
                }
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
            finally
            {
                result.Close();
            }

            return false;
        }

        /// <summary>
        /// Get the numbers of categories in the database.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <returns>Number of categories</returns>
        public int GetArticleCategoryCount()
        {
            MySqlDataReader result = null;
            int count = 0;
            try
            {
                string selectString = String.Format("select count(id)");
                string fromString = String.Format("from `articlecategory`");

                string queryString = String.Format("{0} {1}", selectString, fromString);
                result = Query(queryString.ToString());
                result.Read();
                count = result.GetInt32(0);
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return 0;
            }
            finally
            {
                result.Close();
            }

            return count;
        }

        /// <summary>
        /// Gets the number of categories with no main category in the database.
        /// </summary>
        /// <remarks>
        /// - Requires there is no open Result on connection.
        /// </remarks>
        /// <returns>Number of categories with no main category in the database.</returns>
        public int GetArticleCategoryNoMainCount()
        {
            MySqlDataReader result = null;
            int count = 0;
            try
            {
                string selectString = String.Format("select count(id)");
                string fromString = String.Format("from `articlecategory` where mainCategory is null");

                string queryString = String.Format("{0} {1}", selectString, fromString);
                result = Query(queryString.ToString());
                result.Read();
                count = result.GetInt32(0);
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return 0;
            }
            finally
            {
                result.Close();
            }

            return count;
        }

        #endregion

        #region Article

        /// <summary>
        /// This method returns a complete article from a given id.
        /// </summary>
        /// <param name="id">The id of the article</param>
        /// <returns>The article for the given id</returns>
        public Model.Article GetArticleFromId(int id)
        {
            MySqlDataReader result = null;

            try
            {
                string selectArticle = "select a.`id` as aId, a.`name` as aName, a.`amount` as aAmount, a.`weight` as aWeight, a.`replacementCost` as aReplacementCost, a.`price` as aPrice, type.`name` as aType, a.`height` as aHeigth, a.`width` as aWidth, a.`depth` as aDepth, a.`stackable` as aStackable, a.`wheels` as aWheels, a.`tiltable` as aTiltable";
                string selectCategory = "cat.id as catId, cat.Name as catName, cat.mainCategory as catMain";
                string selectStockLocation = "a.`stockLocation` as locId, area.area as areaArea, area.definition as areaDefinition, loc.shelf  as locShelf, loc.level as locLevel, loc.section as locSection";
                string from = "from `article` a";
                string joinCategory = "inner join articlecategory cat on a.category = cat.id";
                string joinStockLocation = "inner join stockLocation loc on a.stockLocation = loc.id";
                string joinArea = "inner join stockLocationArea area on loc.area = area.area";
                string joinUnit = "inner join articleunittype type on a.type = type.id";
                string where = String.Format("where a.id = '{0}'", id);

                string queryString = String.Format("{0}, {1}, {2} {3} {4} {5} {6} {7} {8}", selectArticle, selectCategory, selectStockLocation, from, joinCategory, joinStockLocation, joinArea, joinUnit, where);
                result = Query(queryString.ToString());

                if (result == null)
                {
                    result.Close();
                    return null;
                }

                result.Read();
                if (!result.HasRows)
                {
                    result.Close();
                    return null;
                }

                //Extract Area:
                Model.StockLocationArea area = new Model.StockLocationArea();
                area.Area = result.GetString("areaArea");
                area.Definition = result.GetString("areaDefinition");

                //Extract Location:
                Model.StockLocation location = new Model.StockLocation();
                location.Id = result.GetInt32("locId");
                location.Area = area;
                location.AreaCode = area.Area;
                location.Shelf = result.GetString("locShelf");
                location.Level = result.GetString("locLevel");
                location.Section = result.GetString("locSection");

                //Extract Category:
                Model.ArticleCategory category = new Model.ArticleCategory();
                category.Id = result.GetInt32("catId");
                category.Name = result.GetString("catName");
                if (!result.IsDBNull(result.GetOrdinal("catMain")))
                    category.MainCategory = result.GetInt32("catMain");

                //Extract Article
                Model.Article article = new Model.Article();
                article.Id = result.GetInt32("aId");
                article.Category = category;
                article.Name = result.GetString("aName");
                if (!result.IsDBNull(result.GetOrdinal("aAmount")))
                    article.Amount = result.GetDecimal("aAmount");
                article.Weight = result.GetDecimal("aWeight");
                article.StockLocation = location;
                article.ReplacementCostExcl = result.GetDecimal("aReplacementCost");
                article.Price = result.GetDecimal("aPrice");
                article.Type = result.GetString("aType");
                article.Height = result.GetDecimal("aHeigth");
                article.Width = result.GetDecimal("aWidth");
                article.Depth = result.GetDecimal("aDepth");
                article.Stackable = result.GetBoolean("aStackable");
                article.Wheels = result.GetBoolean("aWheels");
                article.Tiltable = result.GetBoolean("aTiltable");
                result.Close();
                return article;
            }
            catch (KeyNotFoundException e)
            {
                Popup.Error("Hittade inte nyckel: " + e);
                return null;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString());
                return null;
            }
            finally
            {
                result.Close();
            }
        }

        /// <summary>
        /// Verifies that the given article id exists in the database
        /// </summary>
        /// <param name="id">The id to be verified</param>
        /// <returns>True if found and unique</returns>
        private bool VerifyArticleExists(int id)
        {
            MySqlDataReader result = null;
            try
            {
                string selectString = String.Format("select count(id)");
                string fromString = String.Format("from `article`");
                string whereString = String.Format("where id = '{0}'", id);

                string queryString = String.Format("{0} {1} {2}", selectString, fromString, whereString);
                result = Query(queryString.ToString());

                if (result == null)
                {
                    result.Close();
                    return false;
                }

                result.Read();
                if (!result.HasRows)
                {
                    result.Close();
                    return false;
                }

                if (result.GetInt16(0) == 1)
                {
                    result.Close();
                    return true;
                }
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
            finally
            {
                result.Close();
            }

            return false;
        }

        /// <summary>
        /// The number of article objects in the database
        /// </summary>
        /// <returns>Number of articles in database</returns>
        private int GetArticleCount()
        {
            MySqlDataReader result = null;
            int count = 0;
            try
            {
                string selectString = String.Format("select count(id)");
                string fromString = String.Format("from `article`");

                string queryString = String.Format("{0} {1}", selectString, fromString);
                result = Query(queryString.ToString());
                result.Read();
                count = result.GetInt32(0);
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return 0;
            }
            finally
            {
                result.Close();
            }

            return count;
        }

        /// <summary>
        /// Updates the given article in the database
        /// </summary>
        /// <param name="article">The article to be updated</param>
        /// <returns>True on succes</returns>
        public bool UpdateArticle(Model.Article article)
        {
            // Verify article exists
            if (!VerifyArticleExists(article.Id))
            {
                Popup.Error("Det finns inget sådant artikelnummer.");
                return false;
            }

            //Verify all foreign keys are valid:
            try
            {
                if (!VerifyArticleCategoryExists(article.Category.Id))
                    throw new Exception("Kategorin kunde inte verifieras.");
                if (!VerifyStockLocationExists(article.StockLocation.Id))
                    throw new Exception("Lagerplatsen kunde inte verifieras");
            }
            catch (Exception e)
            {
                Popup.Error(e);
                return false;
            }

            // Build nonQueryString
            try
            {
                string update = "update `article`";
                string set = String.Format("set category = '{0}', name = '{1}', amount = '{2}', weight = '{3}', stockLocation = '{4}', replacementCost = '{5}', price = '{6}', type = '{7}', height = '{8}', width = '{9}', depth = '{10}', stackable = '{11}', wheels = '{12}', tiltable = '{13}'", article.Category.Id, article.Name, article.Amount, article.Weight, article.StockLocation.Id, article.ReplacementCostExcl, article.Price, article.Type.ElementAt(0), article.Height, article.Weight, article.Depth, article.Stackable, article.Wheels, article.Tiltable);
                string where = String.Format("where id = {0}", article.Id);

                string nonQueryString = String.Format("{0} {1} {2}", update, set, where);
                if (NonQuery(nonQueryString.ToString()) == 1)
                    return true;
                else
                    throw new IndexOutOfRangeException("Indexfel i databas.");;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
        }

        /// <summary>
        /// Saves a new article to the database
        /// </summary>
        /// <param name="article">Article to be saved</param>
        /// <returns>True on success</returns>
        public bool SaveNewArticle(Model.Article article)
        {
            //Verify valid content:
            try
            {
                if (VerifyArticleExists(article.Id))
                    throw new NotSupportedException("Artikelnumret finns redan");
                if (!VerifyArticleCategoryExists(article.Category.Id))
                    throw new NullReferenceException("Ogiltig kategori");
                if (!VerifyStockLocationExists(article.StockLocation.Id))
                {
                    if (Popup.QuestionYesNo("Lagerplatsen finns inte, vill du skapa den?", "Kunde inte hitta lagerplatsen", "question"))
                        SaveNewStockLocation(article.StockLocation);
                    else
                        throw new NullReferenceException("Ogiltig lagerplats");
                }
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }

            // Build nonQueryString
            try
            {
                string insertIntoString = String.Format("insert into `article`");
                string columnString = String.Format("(category, name, amount, weight, stockLocation, replacementCost, price, type, height, width, depth, stackable, wheels, tiltable)");
                string valueString = String.Format("values ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}')", article.Category.Id, article.Name, article.Amount, article.Weight, article.StockLocation.Id, article.ReplacementCostExcl, article.Price, article.Type.ElementAt(0), article.Height, article.Weight, article.Depth, article.Stackable, article.Wheels, article.Tiltable);                

                string nonQueryString = String.Format("{0} {1} {2}", insertIntoString, columnString, valueString);
                if (NonQuery(nonQueryString.ToString()) == 1)
                    return true;
                else
                    throw new IndexOutOfRangeException("Indexfel i databas.");;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
        }

        /// <summary>
        /// Deletes an article from the database
        /// </summary>
        /// <param name="id">id of article to be deleted</param>
        /// <returns>True on success</returns>
        public bool DeleteArticle(int id)
        {
            // Verify article exists
            if (!VerifyArticleExists(id))
            {
                Popup.Error("Den artikel du försöker radera existerar inte?");
                return false;
            }

            // Build nonQueryString
            try
            {
                string insertIntoString = String.Format("delete from `article`");
                string whereString = String.Format("where id = {0}", id);

                string nonQueryString = String.Format("{0} {1}", insertIntoString, whereString);
                if (NonQuery(nonQueryString.ToString()) == 1)
                    return true;
                else
                    throw new IndexOutOfRangeException("Indexfel i databas.");;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return false;
            }
        }

        /// <summary>
        /// Deletes an article from the database
        /// </summary>
        /// <param name="id">id of article to be deleted</param>
        /// <returns>True on success</returns>
        public bool DeleteArticle(Model.Article id) { return DeleteArticle(id.Id); }

        /// <summary>
        /// Gives you a datatable with all information of the articles
        /// </summary>
        /// <returns>a datatable with all information of the articles</returns>
        public DataTable GetArticlesAsDataTable()
        {
            // Create datatable:
            DataTable table = new DataTable();
            List<Model.HeaderList> headers = new List<Model.HeaderList>(c.ArticleDataTableHeaderList);
            int i = 0;
            while (i < headers.Count())
            {
                table.Columns.Add(headers[i].SwedishName, typeof(string));
                i++;
            }

            Model.Article[] articles = GetArticlesAsArray();
            if (articles == null)
                return null;
            
            i = 0;
            while (i < articles.Length)
            {
                string[] row = new string[headers.Count];
                int col = 0;
                while (col < headers.Count)
                {
                    row[col] = articles[i].GetValueForString(headers[col].Name);
                    col++;
                }

                table.Rows.Add(row);
                i++;
            }

            return table;
        }

        private Model.Article[] GetArticlesAsArray()
        {
            MySqlDataReader result = null;
            Model.Article[] articles = new Model.Article[GetArticleCount()];
            try
            {
                string selectArticle = "select a.`id` as aId, a.`name` as aName, a.`amount` as aAmount, a.`weight` as aWeight, a.`replacementCost` as aReplacementCost, a.`price` as aPrice, type.`name` as aType, a.`height` as aHeigth, a.`width` as aWidth, a.`depth` as aDepth, a.`stackable` as aStackable, a.`wheels` as aWheels, a.`tiltable` as aTiltable";
                string selectCategory = "cat.id as catId, cat.Name as catName, cat.mainCategory as catMain";
                string selectStockLocation = "a.`stockLocation` as locId, area.area as areaArea, area.definition as areaDefinition, loc.shelf  as locShelf, loc.level as locLevel, loc.section as locSection";
                string from = "from `article` a";
                string joinCategory = "inner join articlecategory cat on a.category = cat.id";
                string joinStockLocation = "inner join stockLocation loc on a.stockLocation = loc.id";
                string joinArea = "inner join stockLocationArea area on loc.area = area.area";
                string joinUnit = "inner join articleunittype type on a.type = type.id";

                string queryString = String.Format("{0}, {1}, {2} {3} {4} {5} {6} {7}", selectArticle, selectCategory, selectStockLocation, from, joinCategory, joinStockLocation, joinArea, joinUnit);
                result = Query(queryString.ToString());

                if (result == null)
                {
                    result.Close();
                    return null;
                }

                if (!result.HasRows)
                {
                    result.Close();
                    return null;
                }
                int index = 0;
                while (result.Read())
                {
                    //Extract Area:
                    Model.StockLocationArea area = new Model.StockLocationArea();
                    area.Area = result.GetString("areaArea");
                    area.Definition = result.GetString("areaDefinition");

                    //Extract Location:
                    Model.StockLocation location = new Model.StockLocation();
                    location.Id = result.GetInt32("locId");
                    location.Area = area;
                    location.AreaCode = area.Area;
                    location.Shelf = result.GetString("locShelf");
                    location.Level = result.GetString("locLevel");
                    location.Section = result.GetString("locSection");

                    //Extract Category:
                    Model.ArticleCategory category = new Model.ArticleCategory();
                    category.Id = result.GetInt32("catId");
                    category.Name = result.GetString("catName");
                    if (!result.IsDBNull(result.GetOrdinal("catMain")))
                        category.MainCategory = result.GetInt32("catMain");

                    //Extract Article
                    Model.Article article = new Model.Article();
                    article.Id = result.GetInt32("aId");
                    article.Category = category;
                    article.Name = result.GetString("aName");
                    if (!result.IsDBNull(result.GetOrdinal("aAmount")))
                        article.Amount = result.GetDecimal("aAmount");
                    article.Weight = result.GetDecimal("aWeight");
                    article.StockLocation = location;
                    article.ReplacementCostExcl = result.GetDecimal("aReplacementCost");
                    article.Price = result.GetDecimal("aPrice");
                    article.Type = result.GetString("aType");
                    article.Height = result.GetDecimal("aHeigth");
                    article.Width = result.GetDecimal("aWidth");
                    article.Depth = result.GetDecimal("aDepth");
                    article.Stackable = result.GetBoolean("aStackable");
                    article.Wheels = result.GetBoolean("aWheels");
                    article.Tiltable = result.GetBoolean("aTiltable");
                    articles[index] = article;
                    index++;
                }
                result.Close();
                return articles;
            }
            catch (KeyNotFoundException e)
            {
                Popup.Error("Hittade inte nyckel: " + e);
                return null;
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString());
                return null;
            }
            finally
            {
                result.Close();
            }
        }

        public static List<Model.HeaderList> GetHeaderList()
        {
            List<Model.HeaderList> headers = new List<Model.HeaderList>();
            headers.Add(new Model.HeaderList(1, "id", "Artikelkod", true, 50));
            headers.Add(new Model.HeaderList(2, "name", "Benämning", true, 150));
            headers.Add(new Model.HeaderList(3, "amount", "Antal", true, 50));
            headers.Add(new Model.HeaderList(4, "type", "Enhet", false, 50));
            headers.Add(new Model.HeaderList(5, "stockLocation", "Lagerplats", true, 100));
            headers.Add(new Model.HeaderList(6, "stockLocationArea", "Lagerområde", false, 75));
            headers.Add(new Model.HeaderList(7, "priceExcl", "Pris (ex moms)", true, 75));
            headers.Add(new Model.HeaderList(8, "priceIncl", "Pris (inkl moms)", true, 75));
            headers.Add(new Model.HeaderList(9, "replacementCostExcl", "Värde (ex moms)", false, 75));
            headers.Add(new Model.HeaderList(10, "replacementCostIncl", "Värde (inkl moms)", false, 75));
            headers.Add(new Model.HeaderList(11, "heigth", "Höjd", false, 50));
            headers.Add(new Model.HeaderList(12, "width", "Bredd", false, 50));
            headers.Add(new Model.HeaderList(13, "depth", "Djup", false, 50));
            headers.Add(new Model.HeaderList(14, "volume", "Volym", false, 50));
            headers.Add(new Model.HeaderList(15, "weight", "Vikt", false, 50));
            headers.Add(new Model.HeaderList(16, "stackable", "Stackningsbar", false, 40));
            headers.Add(new Model.HeaderList(17, "wheels", "Hjul", false, 40));
            headers.Add(new Model.HeaderList(18, "tiltable", "Tiltbar", false, 40));
            headers.Add(new Model.HeaderList(19, "category", "Kategori", true, 100));
            return headers;
        }

        #endregion

        #region ArticleUnitType

        private int GetUnitTypeCount()
        {
            MySqlDataReader result = null;
            int count = 0;
            try
            {
                string selectString = String.Format("select count(id)");
                string fromString = String.Format("from `articleunittype`");

                string queryString = String.Format("{0} {1}", selectString, fromString);
                result = Query(queryString.ToString());
                result.Read();
                count = result.GetInt32(0);
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return 0;
            }
            finally
            {
                result.Close();
            }

            return count;
        }

        public string[] GetUnitTypeArray()
        {
            List<string> types = new List<string>();

            // Create Query:
            MySqlDataReader result = null;

            try
            {
                string selectString = String.Format("select name");
                string fromString = String.Format("from `articleunittype`");
                string queryString = String.Format("{0} {1}", selectString, fromString);
                result = Query(queryString.ToString());

                while (result.Read())
                    types.Add(result.GetString("name"));
            }
            catch (Exception e)
            {
                Popup.Error(e.ToString(), e.GetType().ToString());
                return null;
            }
            finally
            {
                result.Close();
            }

            string[] returnArray = new string[types.Count];
            int i = 0;
            while (i < types.Count)
            {
                returnArray[i] = types[i];
                i++;
            }
            return returnArray;
        }
        #endregion
    }
}
